/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package vn.com.pps.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import vn.com.pps.entities.Course;

/**
 *
 * @author Dai
 */
public class CourseDAO extends BaseDAO implements ICourseDAO {

    @Override
    public int insert(Course course) {
        System.out.println("@InsertCourse");
        String sqlInsert = "INSERT INTO tblcourses(ID,CourseName,CourseLevelID,CourseType,Schedule,Timeframe,Description,Status,CreatedOn,LastModified,StartedDate) VALUES(?,?,?,?,?,?,?,?,?,?,?)";
        Object[] params = new Object[]{null, course.getCourseName(), course.getCourseLevelID(), course.getCourseType(),
            course.getScheduler(), course.getTimeFrame(), course.getDescription(), course.getStatus(), course.getCreatedDate(), course.getLastModifiedDate(), course.getStartedDate()};
        System.out.println("getCourseName:" + course.getCourseName());
        System.out.println("course.getCourseLevelID:" + course.getCourseLevelID());
        System.out.println("course.getCourseType:" + course.getCourseType());
        System.out.println("course.getScheduler:" + course.getScheduler());
        System.out.println("course.getTimeFrame:" + course.getTimeFrame());
        System.out.println("course.getDescription:" + course.getDescription());
        System.out.println("course.getStatus:" + course.getStatus());
        System.out.println("course.getCreatedDate:" + course.getCreatedDate());
        System.out.println("course.getStartedDate:" + course.getStartedDate());
 
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sqlInsert);
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        psc.addParameter(new SqlParameter("CourseName", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CourseLevelID", Types.INTEGER));
        psc.addParameter(new SqlParameter("CourseType", Types.INTEGER));
        psc.addParameter(new SqlParameter("Schedule", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Timeframe", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Description", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Status", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CreatedOn", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("StartedDate", Types.DATE));

        psc.setReturnGeneratedKeys(true);
        KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params),
                generatedKeyHolder);
        return generatedKeyHolder.getKey().intValue();
    }

    @Override
    public int update(Course course) {
        String sqlUpdate = "UPDATE  tblcourses SET CourseName=?,CourseLevelID=?,CourseType=?,Schedule=?,Timeframe=?,Description=?,Status=?,LastModified=?,StartedDate=? WHERE ID=?";
        Object[] params = new Object[]{course.getCourseName(), course.getCourseLevelID(), course.getCourseType(),
            course.getScheduler(), course.getTimeFrame(), course.getDescription(), course.getStatus(), course.getLastModifiedDate(), course.getStartedDate(),course.getId()};
        PreparedStatementCreatorFactory psc
                = new PreparedStatementCreatorFactory(sqlUpdate);
        
        psc.addParameter(new SqlParameter("CourseName", Types.VARCHAR));
        psc.addParameter(new SqlParameter("CourseLevelID", Types.INTEGER));
        psc.addParameter(new SqlParameter("CourseType", Types.INTEGER));
        psc.addParameter(new SqlParameter("Schedule", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Timeframe", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Description", Types.VARCHAR));
        psc.addParameter(new SqlParameter("Status", Types.VARCHAR));
        psc.addParameter(new SqlParameter("LastModified", Types.TIMESTAMP));
        psc.addParameter(new SqlParameter("StartedDate", Types.DATE));
        psc.addParameter(new SqlParameter("ID", Types.INTEGER));
        getJdbcTemplate().update(psc.newPreparedStatementCreator(params));
        return course.getId();
    }

    @Override
    public int delete(int id) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public Page<Course> getCourses(int pageNo, int pageSize, String whereClause) {
        String sqlQuery = "SELECT tblcourses.ID,tblcourses.CourseName"
                + ",tblcourses.CourseLevelID,tblcourses.CourseType"
                + ",tblcourses.Schedule,tblcourses.Timeframe"
                + ",tblcourses.StartedDate,tblcourses.Description"
                + ",tblCourseStatus.Description Status,tblcourses.CreatedOn,tblcourses.LastModified"
                + ",tblcourselevels.Description LevelName"
                + ",tblcoursetypes.Description CourseTypeName "
                + "FROM tblcourses ,tblcourselevels ,tblcoursetypes,TblCourseStatus "
                + "WHERE tblcourses.CourseLevelID=tblcourselevels.ID "
                + "AND tblcourses.CourseType=tblcoursetypes.ID AND tblcourses.Status=TblCourseStatus.Code AND " + whereClause+" Order by tblcourses.lastModified desc";;
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCourses WHERE " + whereClause +" Order by tblcourses.lastModified desc";
        System.out.println("Search Query::"+sqlQuery);
        
        PaginationHelper<Course> ph = new PaginationHelper<Course>();
        return ph.fetchPage(
                this.getJdbcTemplate(),
                sqlQueryCount,
                sqlQuery,
                null,
                pageNo,
                pageSize, new ParameterizedRowMapper<Course>() {
                    @Override
                    public Course mapRow(ResultSet rs, int i) throws SQLException {
                        Course course = new Course();
                        course.setId(rs.getInt("ID"));
                        course.setCourseName(rs.getString("CourseName"));
                        course.setCourseLevelID(rs.getInt("CourseLevelID"));
                        course.setCourseType(rs.getInt("CourseType"));
                        course.setScheduler(rs.getString("Schedule"));
                        course.setTimeFrame(rs.getString("Timeframe"));
                        course.setDescription(rs.getString("Description"));
                        course.setStatus(rs.getString("Status"));
                        course.setCreatedDate(rs.getTimestamp("CreatedOn"));
                        course.setLastModifiedDate(rs.getTimestamp("LastModified"));
                        course.setStartedDate(rs.getDate("StartedDate"));
                        course.setCourseLevelName(rs.getString("LevelName"));
                        course.setCourseTypeName(rs.getString("CourseTypeName"));
                        return course;
                    }
                });
    }

    @Override
    public int getCount(String whereClause) {
        String sqlQueryCount = "SELECT COUNT(*) FROM TblCourses WHERE " + whereClause;
        return getJdbcTemplate().queryForInt(sqlQueryCount);
    }

    @Override
    public Course getCourse(Integer id) {
        String sqlQuery = "SELECT tblcourses.ID,tblcourses.CourseName"
                + ",tblcourses.CourseLevelID,tblcourses.CourseType"
                + ",tblcourses.Schedule,tblcourses.Timeframe"
                + ",tblcourses.StartedDate,tblcourses.Description"
                + ",tblCourseStatus.Description Status,tblcourses.CreatedOn,tblcourses.LastModified"
                + ",tblcourselevels.Description LevelName"
                + ",tblcoursetypes.Description CourseTypeName "
                + "FROM tblcourses,tblcourselevels,tblcoursetypes,TblCourseStatus "
                + "WHERE tblcourses.CourseLevelID=tblcourselevels.ID "
                + "AND tblcourses.CourseType=tblcoursetypes.ID AND tblcourses.Status=TblCourseStatus.Code AND tblcourses.ID=" + id;
        return (Course) getJdbcTemplate().queryForObject(sqlQuery, new RowMapper() {
            @Override
            public Object mapRow(ResultSet rs, int i) throws SQLException {
                Course course = new Course();
                course.setId(rs.getInt("ID"));
                course.setCourseName(rs.getString("CourseName"));
                course.setCourseLevelID(rs.getInt("CourseLevelID"));
                course.setCourseType(rs.getInt("CourseType"));
                course.setScheduler(rs.getString("Schedule"));
                course.setTimeFrame(rs.getString("Timeframe"));
                course.setDescription(rs.getString("Description"));
                course.setStatus(rs.getString("Status"));
                course.setCreatedDate(rs.getTimestamp("CreatedOn"));
                course.setLastModifiedDate(rs.getTimestamp("LastModified"));
                course.setStartedDate(rs.getDate("StartedDate"));
                course.setCourseLevelName(rs.getString("LevelName"));
                course.setCourseTypeName(rs.getString("CourseTypeName"));
                return course;
            }
        });
    }

}
